create VIEW vwPromotionRating
AS
select Promotion.PromotionId, Promotion.BusinessId,
	isnull(vw2.CountReviews, 0) as CountReviews,
	isnull(vw2.Rating, 0) as Rating, 
	isnull(vw2.RatingRound, 0) as RatingRound,
	convert(varchar(20), isnull(vw2.RatingRound, 0)) + ',' + 
		case when isnull(vw2.RatingRound, 0) BETWEEN 0 AND 2 THEN 'Y' 
			when isnull(vw2.RatingRound, 0) BETWEEN 3 AND 4 THEN 'G' 
		else 'R' end AS RatingText
from Promotion (nolock) left join (select PromotionId, CountReviews,
						case when CountNotZeroRating > 0 then SumRating / CountNotZeroRating else 0 end as Rating,
						round(case when CountNotZeroRating > 0 then SumRating / CountNotZeroRating else 0 end, 0) as RatingRound
						from (select PromotionId, COUNT(*) AS CountReviews, 
								SUM(CONVERT(FLOAT, Rating)) AS SumRating,
								SUM(case when Rating > 0 then 1 else 0 end) AS CountNotZeroRating
								from PromotionReview (nolock)
								group by PromotionId) AS vw1) as vw2 on Promotion.PromotionId = vw2.PromotionId
GO
create VIEW vwBusinessRating
AS
select Business.BusinessId, 
	isnull(vw2.CountReviews, 0) as CountReviews,
	isnull(vw2.Rating, 0) as Rating, 
	isnull(vw2.RatingRound, 0) as RatingRound,
	convert(varchar(20), isnull(vw2.RatingRound, 0)) + ',' + 
		case when isnull(vw2.RatingRound, 0) BETWEEN 0 AND 2 THEN 'Y' 
			when isnull(vw2.RatingRound, 0) BETWEEN 3 AND 4 THEN 'G' 
		else 'R' end AS RatingText
from Business (nolock) left join (select BusinessId, CountReviews,
						case when CountNotZeroRating > 0 then SumRating / CountNotZeroRating else 0 end as Rating,
						round(case when CountNotZeroRating > 0 then SumRating / CountNotZeroRating else 0 end, 0) as RatingRound
						from (select BusinessId, COUNT(*) AS CountReviews, 
								SUM(CONVERT(FLOAT, Rating)) AS SumRating,
								SUM(case when Rating > 0 then 1 else 0 end) AS CountNotZeroRating
								from BusinessReview (nolock)
								group by BusinessId) AS vw1) as vw2 on Business.BusinessId = vw2.BusinessId
GO
ALTER PROCEDURE [dbo].[spAPI_Business_GetDeals]
(
	@Token VARCHAR(255),
	@Latitude FLOAT,
	@Longtitude FLOAT,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'


	DECLARE @IdResult INT
	DECLARE @UserId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		if @returnAsDataset = 0
		begin
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.Name AS PromotionName,
									Business.BusinessId, Business.BusinessName,
									STR(dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
									vwBusinessRating.RatingText AS Rating,
									vwPromotionRating.RatingText AS PromotionRating,
									vwBusinessRating.CountReviews AS Reviews,
									vwPromotionRating.CountReviews AS PromotionReviews, 
									15 AS PriceRange,
									Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
									Business.Latitude, Business.Longitude,
									Promotion.PromotionText, Business.[Description], 
									
									Business.BusinessPhone, Business.CellPhone, 
									Business.TollFreePhone, Business.Web,
									
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
										FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
										WHERE BusinessCategory.BusinessId = Business.BusinessId
										FOR XML AUTO, TYPE) AS Categories,
									(SELECT [WeekDay] AS Name, 
										dbo.fnAPI_Format_Time(FromTime) AS Start, 
										dbo.fnAPI_Format_Time(ToTime) AS [End], 
										Closed
									FROM BusinessHours AS [Day]
									WHERE [Day].BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS BusinessHours,
									ISNULL(vwLike.Likes, 0) AS Likes,
									ISNULL(vwLike.Dislikes, 0) AS Dislikes,
									ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft,
									
									ISNULL(vwLikePromotion.Likes, 0) AS PromotionLikes,
									ISNULL(vwLikePromotion.Dislikes, 0) AS PromotionDislikes,
									ISNULL(vwLikePromotion.IsFeedbackLeft, 0) AS PromotionIsFeedbackLeft,
									
									case when uf.BusinessId is null then 0 else 1 end as IsFavorite,
									vwCheckin.LastCheckinDate
								FROM PromotionPush INNER JOIN Promotion ON PromotionPush.PromotionId = Promotion.PromotionId
									LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
									LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
									LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
									LEFT JOIN vwPromotionRating ON Promotion.PromotionId = vwPromotionRating.PromotionId
									LEFT JOIN (SELECT BusinessId,
												SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
												SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
												CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
											FROM BusinessLike
											GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
									LEFT JOIN (SELECT PromotionId,
												SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
												SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
												CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
											FROM PromotionLike
											GROUP BY PromotionId) AS vwLikePromotion ON Promotion.PromotionId = vwLikePromotion.PromotionId
									LEFT JOIN BlockBusiness bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
									LEFT JOIN BlockPromotion bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
									LEFT JOIN UserFavorites uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
									LEFT JOIN (SELECT PromotionCheckin.PromotionId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
												FROM PromotionCheckin
												WHERE PromotionCheckin.UserId = @UserId
												GROUP BY PromotionCheckin.PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
								WHERE (PromotionPush.UserId = @UserId)
									AND (bb.UserId IS NULL)
									AND (bp.UserId IS NULL)

									AND (
											((Promotion.ProximityRadiusId not in (12, 13)) AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280)))
											-- over 200 miles
											OR ((Promotion.ProximityRadiusId = 13) AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) > CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280)))
											-- custom range
											OR ((Promotion.ProximityRadiusId = 12) AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, Promotion.CustomRange)))
										)

									AND (Promotion.StatusCode = 'ACT')
									AND (Promotion.Deleted = 0)
									AND (Promotion.Ready = 1)
									AND (Business.IsActive = 1)
									AND (NOT EXISTS(SELECT 1 
													FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
													WHERE pc.PromotionId = Promotion.PromotionId))
									/*AND (EXISTS(SELECT 1 
												FROM PromotionCategory AS pc INNER JOIN fnAPI_UserCategory_All(@UserId) AS uc ON pc.CategoryId = uc.CategoryId
												WHERE pc.PromotionId = Promotion.PromotionId))*/
									AND DATEDIFF(DAY, PromotionPush.DatePush, GETDATE()) <= 7
									AND (dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay) = 1)
											
											/*(Promotion.BaseCostTimingId <> 5)
											or
											-- Ongoing, Scheduled timing
											(
												CONVERT(TIME, @Now) BETWEEN CONVERT(TIME, ISNULL(Promotion.BaseCostTimingStartsWithinDay, @BegTime))
												AND CONVERT(TIME, ISNULL(Promotion.BaseCostTimingEndsWithinDay, @EndTime))
											)
										)*/

								ORDER BY PromotionPush.DatePush DESC
								FOR XML PATH ('Deal'), ROOT('Deals'))
		end
		else
		begin
			SELECT
				Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
				ProximityRadius.FromRadius as ProximityFromRadius, ProximityRadius.ToRadius as ProximityToRadius,
				Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
				Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
				Promotion.[BaseCostTimingId], BaseCostTiming.Timing as BaseCostTimingName,
				Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
				Promotion.[CampaignBudget], Promotion.[Name],
				Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
				Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
				Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
				Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
				Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
				
				ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
				
				vwBusinessRating.RatingText AS Rating,
				vwPromotionRating.RatingText AS PromotionRating,
				vwBusinessRating.CountReviews AS Reviews,
				vwPromotionRating.CountReviews AS PromotionReviews, 
				
				CONVERT(INT, 0) AS MaxPushes, 
				CONVERT(MONEY, 0) AS SpentMoney, CONVERT(INT, 0) AS SpentPushes, 
				CONVERT(MONEY, 0) AS UnusedMoney, CONVERT(INT, 0) AS UnusedPushes, 
							
				case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
				else -1 
				end as MinutesLeft,
				
				Picture = null,
				PictureContentType = null
								
			FROM PromotionPush INNER JOIN Promotion ON PromotionPush.PromotionId = Promotion.PromotionId
				LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
				LEFT JOIN BaseCostTiming ON Promotion.BaseCostTimingId = BaseCostTiming.BaseCostTimingId
				LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
				LEFT JOIN BlockBusiness bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
				LEFT JOIN BlockPromotion bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
				LEFT JOIN (SELECT PromotionCheckin.PromotionId, COUNT(*) AS CheckIns, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
							FROM PromotionCheckin
							WHERE PromotionCheckin.UserId = @UserId
							GROUP BY PromotionCheckin.PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
				LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
				LEFT JOIN vwPromotionRating ON Promotion.PromotionId = vwPromotionRating.PromotionId
				/*
				LEFT JOIN (SELECT BusinessId,
							SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
							SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
							CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
						FROM BusinessLike
						GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
				
				LEFT JOIN UserFavorites uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
				*/
			WHERE (PromotionPush.UserId = @UserId)
				AND (bb.UserId IS NULL)
				AND (bp.UserId IS NULL)
				/*AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
				--AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))	*/
				AND (Promotion.StatusCode = 'ACT')
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)
				AND (Business.IsActive = 1)
				AND (NOT EXISTS(SELECT 1 
								FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
								WHERE pc.PromotionId = Promotion.PromotionId))
				/*AND (EXISTS(SELECT 1 
							FROM PromotionCategory AS pc INNER JOIN fnAPI_UserCategory_All(@UserId) AS uc ON pc.CategoryId = uc.CategoryId
							WHERE pc.PromotionId = Promotion.PromotionId))*/
				AND DATEDIFF(DAY, PromotionPush.DatePush, GETDATE()) <= 7				
			ORDER BY PromotionPush.DatePush DESc
			
			return
		end
	end

	if @returnAsDataset = 0	
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_GetProfile]
(
	@Token VARCHAR(255),
	@returnAsDataset bit = 0,
	@BusinessId INT = null
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT = 1
	
	DECLARE @Result XML
	
	if @BusinessId is null
	begin
		SET @IdResult = 0
		EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	end
	
	IF @IdResult = 1
	begin		
		SELECT @Result = (SELECT
								Business.[BusinessId],
								Business.[Login],
								Business.[Email],
								Business.[Password],
								Business.[BusinessName],
								Business.[Web],
								Business.[BusinessPhone],
								Business.[CellPhone],
								Business.[TollFreePhone],
								Business.[Fax],
								Business.[StreetAddress1],
								Business.[StreetAddress2],
								Business.[IsActive],
								Business.[Deleted],
								Business.[City],
								Business.[StateCode],
								Business.[ZipCode],
								Business.[Description],
								Business.[CreditCardNo],
								Business.[CreditCardExpDate],
								Business.[CreditCardName],
								Business.[CreditCardPin],
								Business.[BillingAddress1],
								Business.[BillingAddress2],
								Business.[BillingCity],
								Business.[BillingStateCode],
								Business.[BillingZipCode],
								Business.[BillingUse],
								Business.[ReferencedBy],
								Business.[Latitude],
								Business.[Longitude],								
								Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day]
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours,
								vwLike.Likes, vwLike.Dislikes
							FROM Business LEFT JOIN (SELECT BusinessId,
														SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
														SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes
													FROM BusinessLike
													WHERE BusinessId = @BusinessId
													GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
							WHERE Business.BusinessId = @BusinessId
							FOR XML PATH ('Profile'))
							
		if @returnAsDataset = 1
		begin
			SELECT
				Business.[BusinessId],
				Business.[Login],
				Business.[Email],
				Business.[Password],
				Business.[BusinessName],
				Business.[Web],
				Business.[BusinessPhone],
				Business.[CellPhone],
				Business.[TollFreePhone],
				Business.[Fax],
				Business.[StreetAddress1],
				Business.[StreetAddress2],
				Business.[IsActive],
				Business.[Deleted],
				Business.[City],
				Business.[StateCode],
				Business.[ZipCode],
				Business.[Description],
				Business.[CreditCardNo],
				Business.[CreditCardExpDate],
				Business.[CreditCardName],
				Business.[CreditCardPin],
				Business.[BillingAddress1],
				Business.[BillingAddress2],
				Business.[BillingCity],
				Business.[BillingStateCode],
				Business.[BillingZipCode],
				Business.[BillingUse],
				Business.[ReferencedBy],
				Business.[Latitude],
				Business.[Longitude],
				Business.DateAdded,
				Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
				vwLike.Likes, vwLike.Dislikes,
				Picture,
				PictureContentType,
				vwBusinessRating.Rating,
				vwBusinessRating.CountReviews AS ReviewsCount
			FROM Business LEFT JOIN (SELECT BusinessId,
										SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
										SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes
									FROM BusinessLike
									WHERE BusinessId = @BusinessId
									GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
				LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
									
			WHERE Business.BusinessId = @BusinessId		
			
			SELECT Category.CategoryId, Category.CategoryName
			FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
			WHERE BusinessCategory.BusinessId = @BusinessId
			
			SELECT [WeekDay], 
				dbo.fnAPI_Format_Time(FromTime) AS Start, 
				dbo.fnAPI_Format_Time(ToTime) AS [End], 
				Closed
			FROM BusinessHours
			WHERE BusinessId = @BusinessId
			
			return
		end
	end
	
	if @returnAsDataset = 0	
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotionDetails]
(
	@Token VARCHAR(255),
	@PromotionId INT,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
	IF @IdResult = 1
		IF EXISTS(SELECT 1 FROM Promotion WHERE BusinessId = @BusinessId AND PromotionId = @PromotionId)
		BEGIN
			SELECT @Result = (SELECT 
									Promotion.[PromotionId],
									Promotion.[BusinessId],
									Promotion.[ProximityRadiusId],
									pr.FromRadius as ProximityFromRadius,
									pr.ToRadius as ProximityToRadius,
									Promotion.[CustomRange],
									Promotion.[Over200MilesFlag],
									Promotion.[RegisteredOutsideOfZip],
									Promotion.[RegisteredOutsideOfState],
									Promotion.[RegisteredOutsideOfCity],
									Promotion.[BaseCostTimingId],
									bct.Timing as BaseCostTimingName,
									Promotion.[BaseCostTimingScheduledAt],
									Promotion.[BaseCostTimingStarts],
									Promotion.[BaseCostTimingEnds],
									Promotion.[CampaignBudget],
									Promotion.[Name],
									Promotion.[PromotionText],
									Promotion.[PromixityRadiusFixedCost],
									Promotion.[PromixityRadiusRatePerPush],									
									Promotion.[CostTimingFixedCost],
									Promotion.[CostTimingRatePerPush],
									Promotion.BaseCostTimingRatePerPush,
									Promotion.BaseCostTimingFixedCost,
									Promotion.[StatusCode],
									Promotion.[FixedCost],
									Promotion.[RatePerPush],
									Promotion.[CheckinCode],
									Promotion.[Deleted],
									Promotion.[Ready],
									Promotion.BaseCostTimingStartsWithinDay,
									Promotion.BaseCostTimingEndsWithinDay,
									ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
									
									vwBusinessRating.RatingText AS Rating,
									vwBusinessRating.CountReviews AS Reviews,
																		
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END AS MaxPushes,
									
									ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
									ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
									ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
									
									-- Max pushes - Spent Pushes
									CASE WHEN (Promotion.RatePerPush = 0) THEN 0
									ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
									END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
									case
										when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
										else -1 end as MinutesLeft,
										
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name,
											dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
										FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
										WHERE PromotionCategory.PromotionId = Promotion.PromotionId
										FOR XML AUTO, TYPE) AS Categories
								FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
											LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
											LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId,
											
									(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
									FROM PromotionPush
									WHERE PromotionId = @PromotionId) AS vwPushes,
									
									(SELECT SUM(Amount) AS Amount
									FROM PromotionPay
									WHERE PromotionId = @PromotionId) AS vwPay,
									
									(SELECT SUM(FixedCost) AS Amount
									FROM PromotionCost
									WHERE PromotionId = @PromotionId) AS vwPromotionCost,
																		
									(SELECT COUNT(*) AS CheckIns 
									FROM PromotionCheckin 
									WHERE PromotionId = @PromotionId) AS vwCheckin
												
								WHERE (Promotion.PromotionId = @PromotionId)
								FOR XML PATH ('Promotion'))
								
			if @returnAsDataset = 1
			begin
				SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
						pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
						Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
						Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
						Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
						Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
						Promotion.[CampaignBudget], Promotion.[Name],
						Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
						Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
						Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
						Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
						Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
						
						ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
												
						vwBusinessRating.RatingText AS Rating,
						vwBusinessRating.CountReviews AS Reviews,
																					
						CASE WHEN (Promotion.RatePerPush = 0) THEN 0
						ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
						END AS MaxPushes,
												
						ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
						ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
						ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
												
						-- Max pushes - Spent Pushes
						CASE WHEN (Promotion.RatePerPush = 0) THEN 0
						ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
						END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
												
						case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
						else -1 
						end as MinutesLeft,
						
						Picture = null,
						PictureContentType = null
				FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
							LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
							LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId,
							
					(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
						FROM PromotionPush
						WHERE PromotionId = @PromotionId) AS vwPushes,
					
					(SELECT SUM(Amount) AS Amount
						FROM PromotionPay
						WHERE PromotionId = @PromotionId) AS vwPay,
					
					(SELECT SUM(FixedCost) AS Amount
						FROM PromotionCost
						WHERE PromotionId = @PromotionId) AS vwPromotionCost,
														
					(SELECT COUNT(*) AS CheckIns 
						FROM PromotionCheckin 
						WHERE PromotionId = @PromotionId) AS vwCheckin								
				WHERE (Promotion.PromotionId = @PromotionId)
				
				SELECT Category.CategoryId, Category.CategoryName,
						dbo.fnAPI_Category_Path(Category.CategoryId) AS FullPath
				FROM PromotionCategory LEFT JOIN Category ON PromotionCategory.CategoryId = Category.CategoryId
				WHERE PromotionCategory.PromotionId = @PromotionId
				
				return
			end
		END								
		ELSE
			SET @IdResult = -1

	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_GetPromotions]
(
	@Token VARCHAR(255),
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT
	DECLARE @BusinessId INT
	
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_Business_CheckToken @Token = @Token, @Result = @IdResult OUT, @BusinessId = @BusinessId OUT
	
    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'
	
	IF @IdResult = 1
	begin
		SELECT @Result = (SELECT Promotion.PromotionId, Promotion.BusinessId, 
							Promotion.ProximityRadiusId, Promotion.CustomRange, Promotion.Over200MilesFlag, 
							Promotion.RegisteredOutsideOfZip, Promotion.RegisteredOutsideOfState, Promotion.RegisteredOutsideOfCity, 
							Promotion.BaseCostTimingId, Promotion.BaseCostTimingScheduledAt, Promotion.BaseCostTimingStarts, Promotion.BaseCostTimingEnds, 
							Promotion.CampaignBudget, Promotion.Name, Promotion.PromotionText, 
							Promotion.PromixityRadiusFixedCost, Promotion.PromixityRadiusRatePerPush, 
							Promotion.CostTimingFixedCost, Promotion.CostTimingRatePerPush, 
							Promotion.FixedCost, Promotion.RatePerPush, 
							Promotion.CheckinCode, Promotion.Deleted, Promotion.Ready,
							coalesce(vwPush.PushesCount, 0) as PushesCount,
							case
								when Promotion.BaseCostTimingId <> 5 then Promotion.StatusCode
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'COM' then Promotion.StatusCode
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'ACT' and
									-- Ongoing, Scheduled timing
									(
										dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay) = 1
									) then Promotion.StatusCode
								else 'PEN' end as StatusCode,
							case
								when Promotion.BaseCostTimingId <> 5 then PromotionStatus.Name
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'COM' then PromotionStatus.Name
								when Promotion.BaseCostTimingId = 5 and Promotion.StatusCode = 'ACT' and
									-- Ongoing, Scheduled timing
									(
										dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay) = 1
									) then PromotionStatus.Name
								else 'Pending' end as PromotionStatus,
							
							vwBusinessRating.RatingText AS Rating,
							vwBusinessRating.CountReviews AS Reviews
									
							FROM Promotion LEFT JOIN PromotionStatus ON Promotion.StatusCode = PromotionStatus.StatusCode
											LEFT JOIN (SELECT PromotionId, COUNT(*) AS PushesCount
													FROM PromotionPush
													GROUP BY PromotionId) AS vwPush ON Promotion.PromotionId = vwPush.PromotionId
											LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
							WHERE (Promotion.BusinessId = @BusinessId)
								AND (Promotion.Deleted = 0)
								AND (Promotion.Ready = 1)
							FOR XML PATH ('Promotion'), ROOT('Promotions'))
							
		if @returnAsDataset = 1
		begin
			SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
				pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
				Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
				Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
				Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
				Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
				Promotion.[CampaignBudget], Promotion.[Name],
				Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
				Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
				Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
				Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
				Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
			
				ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
								
				vwBusinessRating.RatingText AS Rating,
				vwBusinessRating.CountReviews AS Reviews,
																		
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END AS MaxPushes,
									
				ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
				ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
				ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
									
				-- Max pushes - Spent Pushes
				CASE WHEN (Promotion.RatePerPush = 0) THEN 0
				ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
				END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
				case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
				else -1 
				end as MinutesLeft,
			
				Picture = null,
				PictureContentType = null
			FROM Promotion LEFT JOIN PromotionStatus ON Promotion.StatusCode = PromotionStatus.StatusCode
							LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
							LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS PushesCount
										FROM PromotionPush
										GROUP BY PromotionId) AS vwPush ON Promotion.PromotionId = vwPush.PromotionId
							LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
										FROM PromotionCheckin 
										GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
										FROM PromotionPush
										GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
							LEFT JOIN (SELECT PromotionId, SUM(Amount) AS Amount
										FROM PromotionPay
										GROUP BY PromotionId) AS vwPay ON Promotion.PromotionId = vwPay.PromotionId
							LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
										FROM PromotionCost
										GROUP BY PromotionId) AS vwPromotionCost ON Promotion.PromotionId = vwPromotionCost.PromotionId
			WHERE (Promotion.BusinessId = @BusinessId)
				AND (Promotion.Deleted = 0)
				AND (Promotion.Ready = 1)		
				
			RETURN
		END
	END
	
	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_Nearby]
(
	@Token VARCHAR(255),
	@Lat FLOAT,
	@Long FLOAT,
	@returnAsDataset bit = 0,
	@searchString varchar(100) = ''
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT, @UserId INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		if @returnAsDataset = 0
		begin
			SELECT @Result = (SELECT top(50) Business.BusinessId, Business.BusinessName,
							STR(dbo.fnCalculateDistance(@Lat, @Long, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
									vwBusinessRating.RatingText AS Rating,
									vwBusinessRating.CountReviews AS Reviews,
									15 AS PriceRange,
									Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
									Business.[Description], Business.BusinessPhone, Business.CellPhone, 
									Business.TollFreePhone, Business.Web,
									Business.Latitude, Business.Longitude,
									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
										FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
										WHERE BusinessCategory.BusinessId = Business.BusinessId
										FOR XML AUTO, TYPE) AS Categories,
									(SELECT [WeekDay] AS Name, 
										dbo.fnAPI_Format_Time(FromTime) AS Start, 
										dbo.fnAPI_Format_Time(ToTime) AS [End], 
										Closed
									FROM BusinessHours AS [Day]
									WHERE [Day].BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS BusinessHours,
									ISNULL(vwLike.Likes, 0) AS Likes, 
									ISNULL(vwLike.Dislikes, 0) AS Dislikes, 
									ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft,
									case when uf.BusinessId is null then 0 else 1 end as IsFavorite,
									vwCheckin.LastCheckinDate
								FROM Business LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
											LEFT JOIN (SELECT BusinessId,
															SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
															SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
															CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
														FROM BusinessLike
														GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId			
											LEFT JOIN BlockBusiness AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
											LEFT JOIN UserFavorites AS uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
											LEFT JOIN (SELECT Promotion.BusinessId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
														FROM PromotionCheckin LEFT JOIN Promotion ON PromotionCheckin.PromotionId = Promotion.PromotionId
														WHERE PromotionCheckin.UserId = @UserId
														GROUP BY Promotion.BusinessId) AS vwCheckin ON Business.BusinessId = vwCheckin.BusinessId
								WHERE (Business.IsActive = 1)
									AND (bb.UserId IS NULL)
								FOR XML PATH ('Business'), ROOT('Businesses'))
		end
		else
		begin
			SELECT
				Business.BusinessId,
				Business.[Login],
				Business.Email,
				Business.[Password],
				Business.BusinessName, 
				Business.Web,
				Business.BusinessPhone,
				Business.CellPhone,
				Business.TollFreePhone,
				Business.Fax,
				Business.StreetAddress1,
				Business.StreetAddress2, 
				Business.IsActive,
				Business.Deleted, 
				Business.City,
				Business.StateCode,
				Business.ZipCode,
				Business.[Description], 
				Business.CreditCardNo,
				Business.CreditCardExpDate,
				Business.CreditCardName,
				Business.CreditCardPin, 
				Business.BillingAddress1,
				Business.BillingAddress2,
				Business.BillingCity,
				Business.BillingStateCode,
				Business.BillingZipCode,
				Business.BillingUse, 
				Business.Latitude,
				Business.Longitude, 
				CONVERT(VARBINARY(MAX), NULL) AS Picture,
				Business.PictureContentType, 
				Business.ReferencedBy,
				Business.DateAdded,
				Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
				isnull(vwLike.Likes, 0) as Likes,
				isnull(vwLike.Dislikes, 0) as Dislikes,
				vwBusinessRating.Rating,
				vwBusinessRating.CountReviews AS ReviewsCount
			FROM Business LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
						LEFT JOIN (SELECT BusinessId,
										SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
										SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
										CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
									FROM BusinessLike
									GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId			
						LEFT JOIN BlockBusiness AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
						LEFT JOIN UserFavorites AS uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
						LEFT JOIN (SELECT Promotion.BusinessId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
									FROM PromotionCheckin LEFT JOIN Promotion ON PromotionCheckin.PromotionId = Promotion.PromotionId
									WHERE PromotionCheckin.UserId = @UserId
									GROUP BY Promotion.BusinessId) AS vwCheckin ON Business.BusinessId = vwCheckin.BusinessId											
			WHERE (Business.IsActive = 1)
				AND (bb.UserId IS NULL)	
				and
				(
					(Business.BusinessName like '%' + @searchString + '%')
					or (Business.[Description] like '%' + @searchString + '%')
					or exists(select 1 
								from BusinessCategory left join Category on BusinessCategory.CategoryId = Category.CategoryId 
								where (Business.BusinessId = BusinessCategory.BusinessId)
									and (Category.CategoryName like '%' + @searchString + '%'))
				)
						
			return
		end		
	end

	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_Business_Nearby_GetByZip]
(
	@Token VARCHAR(255),
	@zipCode VARCHAR(255),
	@searchString varchar(100) = ''
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @IdResult INT, @UserId INT
	DECLARE @Result XML	
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		declare @city varchar(100)
		set @city = (select City from ZatifyGEO..ZipCode (nolock) where ZipCode = @zipCode)
		
		SELECT
			Business.BusinessId,
			Business.[Login],
			Business.Email,
			Business.[Password],
			Business.BusinessName, 
			Business.Web,
			Business.BusinessPhone,
			Business.CellPhone,
			Business.TollFreePhone,
			Business.Fax,
			Business.StreetAddress1,
			Business.StreetAddress2, 
			Business.IsActive,
			Business.Deleted, 
			Business.City,
			Business.StateCode,
			Business.ZipCode,
			Business.[Description], 
			Business.CreditCardNo,
			Business.CreditCardExpDate,
			Business.CreditCardName,
			Business.CreditCardPin, 
			Business.BillingAddress1,
			Business.BillingAddress2,
			Business.BillingCity,
			Business.BillingStateCode,
			Business.BillingZipCode,
			Business.BillingUse, 
			Business.Latitude,
			Business.Longitude, 
			CONVERT(VARBINARY(MAX), NULL) AS Picture,
			Business.PictureContentType, 
			Business.ReferencedBy,
			Business.DateAdded,
			Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
			isnull(vwLike.Likes, 0) as Likes,
			isnull(vwLike.Dislikes, 0) as Dislikes,
			vwBusinessRating.Rating,
			vwBusinessRating.CountReviews AS ReviewsCount
		FROM Business LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
					LEFT JOIN (SELECT BusinessId,
									SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
									SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
									CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
								FROM BusinessLike
								GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId			
					LEFT JOIN BlockBusiness AS bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
					LEFT JOIN UserFavorites AS uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
					LEFT JOIN (SELECT Promotion.BusinessId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
								FROM PromotionCheckin LEFT JOIN Promotion ON PromotionCheckin.PromotionId = Promotion.PromotionId
								WHERE PromotionCheckin.UserId = @UserId
								GROUP BY Promotion.BusinessId) AS vwCheckin ON Business.BusinessId = vwCheckin.BusinessId											
		WHERE (Business.IsActive = 1)
			AND (bb.UserId IS NULL)	
			and (Business.City = @city)
			and
			(
				(Business.BusinessName like '%' + @searchString + '%')
				or (Business.[Description] like '%' + @searchString + '%')
				or exists(select 1 
							from BusinessCategory left join Category on BusinessCategory.CategoryId = Category.CategoryId 
							where (Business.BusinessId = BusinessCategory.BusinessId)
								and (Category.CategoryName like '%' + @searchString + '%'))
			)
	end
END
GO
ALTER PROCEDURE [dbo].[spAPI_Promotion_Push]
-- Use in other SP!!!
-- Not for call directly
(
	@UserId INT,
	@Latitude FLOAT,
	@Longitude FLOAT,
	@LocalTime DATETIME = NULL,
	
	@Result XML OUT
)
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT ON
	
	declare @promotionIdCurr int
	
	CREATE TABLE #tmpPromotions
	(
		PromotionId INT,
		RatePerPush MONEY,
		Lat FLOAT,
		Lon FLOAT,
		WillStop bit default(0)
	)
		
	BEGIN TRANSACTION
			INSERT INTO #tmpPromotions
			(PromotionId, RatePerPush, Lat, Lon)
			SELECT PromotionId, RatePerPush, Latitude, Longitude
			FROM PromotionPushQueue (NOLOCK)
			WHERE UserId = @UserId
			
			if @@error <> 0 return
				
			SELECT @Result = (SELECT Promotion.PromotionId, Promotion.PromotionText, Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								vwBusinessRating.RatingText AS Rating,
								vwBusinessRating.CountReviews AS Reviews,
								15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory (NOLOCK) LEFT JOIN Category (NOLOCK) ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day] (NOLOCK)
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours
							FROM #tmpPromotions pp (nolock) INNER JOIN Promotion (NOLOCK) ON pp.PromotionId = Promotion.PromotionId
											LEFT JOIN Business (NOLOCK) ON Promotion.BusinessId = Business.BusinessId
											LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
													--WHERE pp.UserId = @UserId
							FOR XML PATH ('Promotion'))
							
			INSERT INTO PromotionPush
			(PromotionId, UserId, DatePush, Latitude, Longitude, RatePerPush)
			SELECT PromotionId, @UserId, GETUTCDATE(), Lat, Lon, RatePerPush
			FROM #tmpPromotions
			--WHERE (NOT EXISTS(SELECT 1 FROM PromotionPush WHERE (PromotionPush.UserId = @UserId) AND (PromotionPush.PromotionId = #tmpPromotions.PromotionId)))
			
			if @@error <> 0 return
			
			DELETE
			FROM PromotionPushQueue
			WHERE (UserId = @UserId)
				AND (PromotionId IN (SELECT PromotionId FROM #tmpPromotions))

			if @@error <> 0 return
			
			update #tmpPromotions
			set WillStop = 1
			from Promotion LEFT JOIN (SELECT PromotionId, SUM(RatePerPush) AS UsedBudget
											FROM PromotionPush
											GROUP BY PromotionId) AS vwPush ON Promotion.PromotionId = vwPush.PromotionId
							LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
											FROM PromotionCost
											GROUP BY PromotionId) AS vwPromotionCost ON Promotion.PromotionId = vwPromotionCost.PromotionId
			where (Promotion.PromotionId = #tmpPromotions.PromotionId)
				AND (Promotion.CampaignBudget - ISNULL(vwPush.UsedBudget, 0) - ISNULL(vwPromotionCost.Amount, 0) < Promotion.RatePerPush)
						
			/*UPDATE Promotion
			SET StatusCode = 'COM'
			FROM #tmpPromotions LEFT JOIN (SELECT PromotionId, SUM(RatePerPush) AS UsedBudget
											FROM PromotionPush
											GROUP BY PromotionId) AS vwPush ON #tmpPromotions.PromotionId = vwPush.PromotionId
								LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
											FROM PromotionCost
											GROUP BY PromotionId) AS vwPromotionCost ON #tmpPromotions.PromotionId = vwPromotionCost.PromotionId
			WHERE (Promotion.PromotionId = #tmpPromotions.PromotionId)
				AND (Promotion.CampaignBudget - ISNULL(vwPush.UsedBudget, 0) - ISNULL(vwPromotionCost.Amount, 0) < Promotion.RatePerPush)*/
				
			update Promotion
			set StatusCode = 'COM'
			from #tmpPromotions				
			where (Promotion.PromotionId = #tmpPromotions.PromotionId)
				and (#tmpPromotions.WillStop = 1)
				
				
			-- Calc rest and save rest
			declare cursorStopped cursor for 
			select PromotionId
			from #tmpPromotions
			where WillStop = 1
			
			open cursorStopped
			fetch from cursorStopped into @promotionIdCurr
			
			while @@FETCH_STATUS = 0
			begin
				exec spPromotionRest_Add @PromotionId = @promotionIdCurr
				fetch from cursorStopped into @promotionIdCurr
			end
			
			close cursorStopped
			deallocate cursorStopped
			
			if @@error <> 0 return
			
	COMMIT TRANSACTION
		
	DROP TABLE #tmpPromotions
END
GO
ALTER PROCEDURE [dbo].[spAPI_User_GetAllActivePromotionsByBusiness]
(
	@Token VARCHAR(255),
	@businessId int,
	@Latitude float = null,
	@Longitude float = null
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
    DECLARE @Now DATETIME, @BegTime DATETIME, @EndTime DATETIME
    SET @Now = GETUTCDATE()
	SET @BegTime = '00:00:00'
	SET @EndTime = '23:59:59'

	
	DECLARE @IdResult INT
	
	DECLARE @Result XML
	declare @userId int
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @userId = @userId out
	
	IF @IdResult = 1
		SELECT @Result = (SELECT Promotion.PromotionId, Promotion.Name AS PromotionName,
								Business.BusinessId, Business.BusinessName,
								STR(dbo.fnCalculateDistance(@Latitude, @Longitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
								0 AS Distance,
								vwBusinessRating.RatingText AS Rating,
								vwBusinessRating.CountReviews AS Reviews,
								15 AS PriceRange,
								Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
								Business.Latitude, Business.Longitude,
								Promotion.PromotionText, Business.[Description], 
								
								Business.BusinessPhone, Business.CellPhone, 
								Business.TollFreePhone, Business.Web,
								
								(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
									FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
									WHERE BusinessCategory.BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS Categories,
								(SELECT [WeekDay] AS Name, 
									dbo.fnAPI_Format_Time(FromTime) AS Start, 
									dbo.fnAPI_Format_Time(ToTime) AS [End], 
									Closed
								FROM BusinessHours AS [Day]
								WHERE [Day].BusinessId = Business.BusinessId
								FOR XML AUTO, TYPE) AS BusinessHours,
								ISNULL(vwLike.Likes, 0) AS Likes,
								ISNULL(vwLike.Dislikes, 0) AS Dislikes,
								ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft,
								
								case when uf.BusinessId is null then 0 else 1 end as IsFavorite,
								vwCheckin.LastCheckinDate
							FROM Promotion
								LEFT JOIN Business ON Promotion.BusinessId = Business.BusinessId
								LEFT JOIN ProximityRadius ON Promotion.ProximityRadiusId = ProximityRadius.ProximityRadiusId
								LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
								LEFT JOIN (SELECT BusinessId,
											SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
											SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
											CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
										FROM BusinessLike
										GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
								LEFT JOIN BlockBusiness bb ON bb.BusinessId = Business.BusinessId AND bb.UserId = @UserId
								LEFT JOIN BlockPromotion bp ON bp.PromotionId = Promotion.PromotionId AND bp.UserId = @UserId
								LEFT JOIN UserFavorites uf ON uf.BusinessId = Business.BusinessId AND uf.UserId = @UserId
								LEFT JOIN (SELECT PromotionCheckin.PromotionId, MAX(PromotionCheckin.DateCheckin) AS LastCheckinDate 
											FROM PromotionCheckin
											WHERE PromotionCheckin.UserId = @UserId
											GROUP BY PromotionCheckin.PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
							WHERE (Promotion.BusinessId = @BusinessId)
								AND (bb.UserId IS NULL)
								AND (bp.UserId IS NULL)
								/*AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) <= CONVERT(FLOAT, ProximityRadius.ToRadiusFeet / 5280))
								--AND (dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude) >= CONVERT(FLOAT, ProximityRadius.FromRadiusFeet / 5280))	*/
								AND (Promotion.StatusCode = 'ACT')
								AND (Promotion.Deleted = 0)
								AND (Promotion.Ready = 1)
								AND (Business.IsActive = 1)
								AND (NOT EXISTS(SELECT 1 
												FROM PromotionCategory AS pc INNER JOIN fnAPI_BlockCategory_All(@UserId) AS bc ON pc.CategoryId = bc.CategoryId
												WHERE pc.PromotionId = Promotion.PromotionId))
								AND (dbo.fnCheckPromoForWithinDay(Promotion.BaseCostTimingId, Promotion.BaseCostTimingStartsWithinDay,
											Promotion.BaseCostTimingEndsWithinDay) = 1)
										
										/*(Promotion.BaseCostTimingId <> 5)
										or
										-- Ongoing, Scheduled timing
										(
											CONVERT(TIME, @Now) BETWEEN CONVERT(TIME, ISNULL(Promotion.BaseCostTimingStartsWithinDay, @BegTime))
											AND CONVERT(TIME, ISNULL(Promotion.BaseCostTimingEndsWithinDay, @EndTime))
										)
									)	*/											
							FOR XML PATH ('Deal'), ROOT('Deals'))

	SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spAPI_UserFavorites_GetList]
(
	@Token VARCHAR(255),
	@Latitude FLOAT = null,
	@Longtitude FLOAT = null,
	@returnAsDataset bit = 0
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @IdResult INT, @UserId INT
	DECLARE @Result XML
	
	SET @IdResult = 0
	EXEC spAPI_User_CheckToken @Token = @Token, @Result = @IdResult OUT, @UserId = @UserId OUT
	
	IF @IdResult = 1
	begin
		if @returnAsDataset = 0
		begin
			SELECT @Result = (SELECT Business.BusinessId, Business.BusinessName,
									STR(dbo.fnCalculateDistance(@Latitude, @Longtitude, Business.Latitude, Business.Longitude), 38, 2) AS Distance,
									vwBusinessRating.RatingText AS Rating,
									vwBusinessRating.CountReviews AS Reviews,
									15 AS PriceRange,
									Business.StreetAddress1 + ' ' + Business.StreetAddress2 AS [Address],
									Business.[Description], Business.BusinessPhone, Business.CellPhone, 
									Business.TollFreePhone, Business.Web,
									Business.Latitude, Business.Longitude,

									(SELECT Category.CategoryId AS Id, Category.CategoryName AS Name
										FROM BusinessCategory LEFT JOIN Category ON BusinessCategory.CategoryId = Category.CategoryId
										WHERE BusinessCategory.BusinessId = Business.BusinessId
										FOR XML AUTO, TYPE) AS Categories,
									
									(SELECT [WeekDay] AS Name, 
										dbo.fnAPI_Format_Time(FromTime) AS Start, 
										dbo.fnAPI_Format_Time(ToTime) AS [End], 
										Closed
									FROM BusinessHours AS [Day]
									WHERE [Day].BusinessId = Business.BusinessId
									FOR XML AUTO, TYPE) AS BusinessHours,
									ISNULL(vwLike.Likes, 0) AS Likes, ISNULL(vwLike.Dislikes, 0) AS Dislikes, ISNULL(vwLike.IsFeedbackLeft, 0) AS IsFeedbackLeft
								FROM UserToken INNER JOIN [User] ON UserToken.LoginName = [User].[Login]
											INNER JOIN UserFavorites ON [User].UserId = UserFavorites.UserId
											INNER JOIN Business ON UserFavorites.BusinessId = Business.BusinessId
											LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
											LEFT JOIN (SELECT BusinessId,
															SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
															SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes,
															CONVERT(BIT, MAX(CASE WHEN UserId = @UserId THEN 1 ELSE 0 END)) AS IsFeedbackLeft
														FROM BusinessLike
														GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId						
								WHERE UserToken.SecurityToken = @Token
								FOR XML PATH ('Business'), ROOT('Businesses'))
		end
		else
		begin
			SELECT
				Business.BusinessId,
				Business.[Login],
				Business.Email,
				Business.[Password],
				Business.BusinessName, 
				Business.Web,
				Business.BusinessPhone,
				Business.CellPhone,
				Business.TollFreePhone,
				Business.Fax,
				Business.StreetAddress1,
				Business.StreetAddress2, 
				Business.IsActive,
				Business.Deleted, 
				Business.City,
				Business.StateCode,
				Business.ZipCode,
				Business.[Description], 
				Business.CreditCardNo,
				Business.CreditCardExpDate,
				Business.CreditCardName,
				Business.CreditCardPin, 
				Business.BillingAddress1,
				Business.BillingAddress2,
				Business.BillingCity,
				Business.BillingStateCode,
				Business.BillingZipCode,
				Business.BillingUse, 
				Business.Latitude,
				Business.Longitude, 
				Business.DateAdded,
				CONVERT(VARBINARY(MAX), NULL) AS Picture,
				Business.PictureContentType, 
				Business.ReferencedBy,
				Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
				isnull(vwLike.Likes, 0) as Likes,
				isnull(vwLike.Dislikes, 0) as Dislikes,
								
				vwBusinessRating.Rating,
				vwBusinessRating.CountReviews AS ReviewsCount
			FROM UserToken INNER JOIN [User] ON UserToken.LoginName = [User].[Login]
						INNER JOIN UserFavorites ON [User].UserId = UserFavorites.UserId
						INNER JOIN Business ON UserFavorites.BusinessId = Business.BusinessId
						left join
							(SELECT BusinessId,
								SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
								SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes
							FROM BusinessLike (nolock)
							GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId	
						LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
							
			WHERE UserToken.SecurityToken = @Token	
	
			return
		end		
	end
	
	if @returnAsDataset = 0
		SELECT @IdResult AS IdResult, ISNULL(@Result, '') AS Result
END
GO
ALTER PROCEDURE [dbo].[spBusiness_GetById]
	@BusinessId INT
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		Business.*,
		Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
		vwLike.Likes,
		vwLike.Dislikes,
		vwBusinessRating.Rating,
		vwBusinessRating.CountReviews AS ReviewsCount
	FROM Business (NOLOCK) LEFT JOIN
		(SELECT BusinessId,
			SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
			SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes
		FROM BusinessLike (nolock)
		WHERE BusinessId = @BusinessId
		GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId
		LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
	WHERE Business.BusinessId = @BusinessId
END
GO
ALTER PROCEDURE [dbo].[spBusiness_GetList]
AS
BEGIN
	SET NOCOUNT ON

	SELECT
		Business.BusinessId,
		Business.[Login],
		Business.Email,
		Business.[Password],
		Business.BusinessName, 
		Business.Web,
		Business.BusinessPhone,
		Business.CellPhone,
		Business.TollFreePhone,
		Business.Fax,
		Business.StreetAddress1,
		Business.StreetAddress2, 
		Business.IsActive,
		Business.Deleted, 
		Business.City,
		Business.StateCode,
		Business.ZipCode,
		Business.[Description], 
		Business.CreditCardNo,
		Business.CreditCardExpDate,
		Business.CreditCardName,
		Business.CreditCardPin, 
		Business.BillingAddress1,
		Business.BillingAddress2,
		Business.BillingCity,
		Business.BillingStateCode,
		Business.BillingZipCode,
		Business.BillingUse, 
		Business.Latitude,
		Business.Longitude, 
		CONVERT(VARBINARY(MAX), NULL) AS Picture,
		Business.PictureContentType, 
		Business.ReferencedBy,
		Business.DateAdded,
		Open24_7 = dbo.fnIsBusinessOpened24_7(Business.BusinessId),
		vwLike.Likes,
		vwLike.Dislikes,
		vwBusinessRating.Rating,
		vwBusinessRating.CountReviews AS ReviewsCount
	FROM Business (NOLOCK) LEFT JOIN
		(SELECT BusinessId,
			SUM(CASE WHEN IsLiked = 1 THEN 1 ELSE 0 END) AS Likes,
			SUM(CASE WHEN IsLiked = 0 THEN 1 ELSE 0 END) AS Dislikes
		FROM BusinessLike (nolock)
		GROUP BY BusinessId) AS vwLike ON Business.BusinessId = vwLike.BusinessId	
		LEFT JOIN vwBusinessRating ON Business.BusinessId = vwBusinessRating.BusinessId
		
	WHERE Deleted = 0
END
GO
ALTER PROCEDURE [dbo].[spPromotion_GetById]
(
	@PromotionId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
			pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
			Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
			Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
			Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
			Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
			Promotion.[CampaignBudget], Promotion.[Name],
			Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
			Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
			Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
			Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
			Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
			
			ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
						
			vwBusinessRating.RatingText AS Rating,
			vwBusinessRating.CountReviews AS Reviews,
																		
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END AS MaxPushes,
									
			ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
			ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
			ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
									
			-- Max pushes - Spent Pushes
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
			case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
			else -1 
			end as MinutesLeft,
			
			Promotion.Picture,
			Promotion.PictureContentType
										
	FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
					LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
					LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId,
								
											
					(SELECT COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
					FROM PromotionPush
					WHERE PromotionId = @PromotionId) AS vwPushes,
									
					(SELECT SUM(Amount) AS Amount
					FROM PromotionPay
					WHERE PromotionId = @PromotionId) AS vwPay,
									
					(SELECT SUM(FixedCost) AS Amount
					FROM PromotionCost
					WHERE PromotionId = @PromotionId) AS vwPromotionCost,
																		
					(SELECT COUNT(*) AS CheckIns 
					FROM PromotionCheckin 
					WHERE PromotionId = @PromotionId) AS vwCheckin
												
	WHERE (Promotion.PromotionId = @PromotionId)
END
GO
ALTER PROCEDURE [dbo].[spPromotion_GetList]
(
	@BusinessId INT
)
AS
BEGIN
	SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	SELECT Promotion.[PromotionId], Promotion.[BusinessId], Promotion.[ProximityRadiusId],
			pr.FromRadius as ProximityFromRadius, pr.ToRadius as ProximityToRadius,
			Promotion.[CustomRange], Promotion.[Over200MilesFlag], 
			Promotion.[RegisteredOutsideOfZip], Promotion.[RegisteredOutsideOfState], Promotion.[RegisteredOutsideOfCity],
			Promotion.[BaseCostTimingId], bct.Timing as BaseCostTimingName,
			Promotion.[BaseCostTimingScheduledAt], Promotion.[BaseCostTimingStarts], Promotion.[BaseCostTimingEnds],
			Promotion.[CampaignBudget], Promotion.[Name],
			Promotion.[PromotionText], Promotion.[PromixityRadiusFixedCost], Promotion.[PromixityRadiusRatePerPush],									
			Promotion.[CostTimingFixedCost], Promotion.[CostTimingRatePerPush],
			Promotion.BaseCostTimingRatePerPush, Promotion.BaseCostTimingFixedCost,
			Promotion.[StatusCode], Promotion.[FixedCost], Promotion.[RatePerPush],
			Promotion.[CheckinCode], Promotion.[Deleted], Promotion.[Ready],									
			
			ISNULL(vwCheckin.CheckIns, 0) AS CheckIns,
			
			vwBusinessRating.RatingText AS Rating,
			vwBusinessRating.CountReviews AS Reviews,
																		
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END AS MaxPushes,
									
			ISNULL(vwPushes.Amount, 0) + ISNULL(vwPromotionCost.Amount, 0) AS SpentMoney, 
			ISNULL(vwPushes.CountPushes, 0) AS SpentPushes,
			ISNULL(vwPay.Amount, 0) - ISNULL(vwPushes.Amount, 0) - ISNULL(vwPromotionCost.Amount, 0) AS UnusedMoney,
									
			-- Max pushes - Spent Pushes
			CASE WHEN (Promotion.RatePerPush = 0) THEN 0
			ELSE CONVERT(INT, FLOOR((Promotion.CampaignBudget - Promotion.FixedCost) / Promotion.RatePerPush)) 
			END - ISNULL(vwPushes.CountPushes, 0) AS UnusedPushes,
									
			case when Promotion.[BaseCostTimingId] = 5 then abs(datediff(minute, getutcdate(), Promotion.[BaseCostTimingEnds]))
			else -1 
			end as MinutesLeft,
			
			Picture = null,
			PictureContentType = null
										
	FROM Promotion LEFT JOIN BaseCostTiming bct on bct.BaseCostTimingId = Promotion.BaseCostTimingId
					LEFT JOIN ProximityRadius pr on pr.ProximityRadiusId = Promotion.ProximityRadiusId
					LEFT JOIN vwBusinessRating ON Promotion.BusinessId = vwBusinessRating.BusinessId
					LEFT JOIN (SELECT PromotionId, COUNT(*) AS CountPushes, SUM(RatePerPush) AS Amount
								FROM PromotionPush
								GROUP BY PromotionId) AS vwPushes ON Promotion.PromotionId = vwPushes.PromotionId
					LEFT JOIN (SELECT PromotionId, SUM(Amount) AS Amount
								FROM PromotionPay
								GROUP BY PromotionId) AS vwPay ON Promotion.PromotionId = vwPay.PromotionId
					LEFT JOIN (SELECT PromotionId, SUM(FixedCost) AS Amount
								FROM PromotionCost
								GROUP BY PromotionId) AS vwPromotionCost ON Promotion.PromotionId = vwPromotionCost.PromotionId
					LEFT JOIN (SELECT PromotionId, COUNT(*) AS CheckIns 
								FROM PromotionCheckin 
								GROUP BY PromotionId) AS vwCheckin ON Promotion.PromotionId = vwCheckin.PromotionId
	WHERE (Promotion.BusinessId = @BusinessId)
END
GO